1. Field of the Invention
Embodiments of the present invention relate, in general, to the extraction, transformation and loading of data and particularly to an automated system for extraction, transformation and transmission (loading) of channel market data.
2. Relevant Background
A data warehouse, as is known in the art, is a central repository for all or significant parts of data that an enterprise's various business systems collect. Typically, a data warehouse is housed on an enterprise server computing system. In such a system, data from various sources is selectively extracted and organized on the data warehouse database for use by analytical applications and user queries by the enterprise.
It is known in the art that in managing databases, “extract, transform and load” (ETL) refers to three separate functions combined into a single program procedure. First, the extract function reads data from a specified source database and extracts a desired subset of data. Next, the transform function works with the acquired data—using rules or lookup tables, or creating combinations with other data—to convert it to the desired state. Finally, the load function is used to write the resulting data (either the entirety of the subset or only the changes) to a target database, which may or may not previously exist. ETL procedures can be used to acquire a subset of data for reports or other purposes, such as the migration of data from one database or platform to another.
In a channel market environment, a end seller of goods and services may transact with a number of manufacturers to provide the end buyer (also referred to herein as buyer) with a wide selection of products and services (collectively referred to herein as goods). As these goods are sold, the end seller communicates to the manufacturer, normally through a distributor, that additional goods are necessary and desired. In one scenario, the manufacturer moves to supply the increased demand. In other scenarios, the varying demand curve is resolved by stock maintained by the distributor, and only when the distributor's stockpiles are low does the manufacturer recognize the need for additional products.
A long felt need by the manufacturer in the channel economy is buyer data with respect to their product. Data from the end seller with respect to what type of buyer ultimately is purchasing a particular type of product is often unavailable to the manufacturer. Even when such data is available, the process by which to convey it to the manufacturers is tedious and costly. Thus the manufacturer relies heavily on information from the distributors whose buyer data may or may not be accurate or complete. Only data gained via an end seller can be assured to be complete and accurate.
Conveying sensitive data with respect to sales is not without risk. While this type of data is critical for the manufacturer to implement strategic plans with respect to product modifications, future research, product development, sales commission, allocation of market development funds, etc., it also may allow a manufacturer to circumvent the role of a distributor or even the retailer. In today's environment of on-line shopping, the brick and mortar stores are not only in competition with across town rivals but with on-line sales and distribution of a product from the manufacturers themselves.
To address the need for channel data, manufacturers, in return for a discount on their product, often negotiate with distributors and retailers alike. In such a negotiation, the distributor or end seller would provide to the manufacturer key information that helps facilitate the production and development of more marketable goods without undermining either the distributor's or retailer's role in the channel. Data that is submitted to the manufacturer typically must follow a rigid format usable by the manufacturer and is but a small subset of the data housed by the providing entity.
From the retailer's, reseller's, agent's, or distributor's (collectively referred to herein as Partners) data warehouse, select pieces of information are extracted, transformed and then conveyed to the manufacturer or suppliers (collectively referred to herein as Suppliers). While ETL tools exist to aid in the process, there remain many tedious and time consuming tasks. For example, providers of the data are understandably reluctant to provide a Supplier or a similar entity access to their entire data warehouse due to the extent of sensitive data contained therein. Thus, even with current ETL tools, many providers manually verify that the information being sent to a particular Supplier does not contain sensitive information prior to it leaving the provider's confines. Similarly, there is a question of timeliness. Data of this sort is very time sensitive, thus the Supplier desires and often demands that the data be conveyed on a regular basis. While compiling this sort of information for a single Supplier on a reoccurring basis may be possible, the problem is compounded as most channel Partners offer a variety of goods from a variety of Suppliers, each of which desires specific and dissimilar data in dissimilar formats. Thus extracting, validating, transforming and conveying channel data about a multitude of goods to a plurality of unique providers on a timely basis remains a challenge.